package pinyinJava.persistence;

import pinyinJava.bean.DBean;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;

public class SqlitePer implements IDbPersistence {

    @Override
    public Connection open(String path) throws SQLException {
        File file = new File(path);
        if (!file.exists()) {
            if (!file.getParentFile().exists()) file.getParentFile().mkdirs();
            try {
                file.createNewFile();
            } catch (IOException e) {
                System.out.println("SqlitePer Err:" + e == null ? "" : e.toString());
            }
        }
        Connection connection = DriverManager.getConnection("jdbc:sqlite:/" + path);
        connection.setAutoCommit(false);
        return connection;
    }

    @Override
    public void close(Connection connection) throws SQLException {
        connection.close();
    }

    @Override
    public void save(Connection connection, String tName, ArrayList<DBean> dList) throws SQLException {
        Statement statement = connection.createStatement();
        statement.execute(String.format("create table if not exists %s (id integer primary key autoincrement, zyCode text, pyCode text, word text, frequency int, use int)", tName));
        statement.execute(String.format("delete from %s", tName));
        connection.commit();
        PreparedStatement preparedStatement = connection.prepareStatement(String.format("insert into %s (zyCode, pyCode, word, frequency, use) values(?, ?, ?, ?, ?)", tName));
        for (DBean d : dList) {
            preparedStatement.setString(1, d.getZyCode());
            preparedStatement.setString(2, d.getPyCode());
            preparedStatement.setString(3, d.getWord());
            preparedStatement.setLong(4, d.getFrequency());
            preparedStatement.setLong(5, d.getUse());
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        connection.commit();
        preparedStatement.close();
    }

    /**
     * 查询所有表并且修改每个表中汉字为繁体，并且增加id字段
     */
    @Override
    public ArrayList<DBean> read(Connection connection, String tName) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from " + tName);
        ArrayList<DBean> dList = new ArrayList<>();
        while (resultSet.next()) {
            String code = resultSet.getString("code");
            String word = resultSet.getString("word");
            long frequency = resultSet.getLong("frequency");
            long use = resultSet.getLong("use");
            DBean d = new DBean(code, word, frequency, use);
            dList.add(d);
        }
        resultSet.close();
        statement.close();
        return dList;
    }

    @Override
    public ArrayList<String> tables(Connection connection) throws SQLException {
        ArrayList<String> list = new ArrayList<>();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select tbl_name from sqlite_master where type='table' and tbl_name!='android_metadata' and tbl_name!='sqlite_sequence'");
        while (resultSet.next()) {
            String tbl_name = resultSet.getString(1);
            list.add(tbl_name);
        }
        resultSet.close();
        statement.close();
        list.sort(new Comparator<String>() {
            @Override
            public int compare(String o1, String o2) {
                char o1a = o1.charAt(o1.length() - 2);
                char o1b = o1.charAt(o1.length() - 1);
                char o2a = o2.charAt(o2.length() - 2);
                char o2b = o2.charAt(o2.length() - 1);
                if (o1a == o2a)
                    return o1b - o2b;
                else
                    return o1a - o2a;
            }
        });
        return list;
    }
}
